<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page trimDirectiveWhitespaces="true"%>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%@ page import = "java.sql.PreparedStatement" %>

<% 
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
			
	
		//////////get permission ///////////
		
		String name = String.valueOf(session.getAttribute("name"));
		String surname = String.valueOf(session.getAttribute("surname"));
		String role_name = String.valueOf(session.getAttribute("student_role"));
		
		String department_id = String.valueOf(session.getAttribute("department_id"));
	
		System.out.println("name : " + name);///////////////////////////////
		System.out.println("department_id : " + department_id);/////////////////////////
		
		
		List<String> system_name = new ArrayList<String>();
		
		Permission permission = new Permission(strUser,name);
		permission.setDepartmentId(department_id);
		permission.setStudentCreate(Boolean.valueOf(String.valueOf(session.getAttribute("student_create"))));
		permission.setStudentDelete(Boolean.valueOf(String.valueOf(session.getAttribute("student_delete"))));
		permission.setStudentRead(Boolean.valueOf(String.valueOf(session.getAttribute("student_read"))));
		permission.setStudentUpdate(Boolean.valueOf(String.valueOf(session.getAttribute("student_update"))));
		
		String permiss = "";
		if(permission.getStudentCreate()) {
			permiss = permiss + "Add";
		}
		if(permission.getStudentUpdate()) {
			if("".equals(permiss)) {
				permiss = permiss + "Edit";
			} else {
				permiss = permiss + " Edit";
			}
		}
		if(permission.getStudentRead()) {
			if("".equals(permiss)) {
				permiss = permiss + "Read";
			} else {
				permiss = permiss + " Read";
			}
		}
		if(permission.getStudentDelete()) {
			if("".equals(permiss)) {
				permiss = permiss + "Delete";
			} else {
				permiss = permiss + " Delete";
			}
		}


	//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	




	System.out.println("student_edit_personal_save Active");
	
	
	java.util.Date dt = new java.util.Date();

	java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

	String date_now = sdf.format(dt);
	
	String personal_id =  request.getParameter("personal_id");
	String title_th =   request.getParameter("title_th");
	String name_th =   request.getParameter("name_th");												
	String surname_th =   request.getParameter("surname_th");								
	String name_en =  request.getParameter("name_en");	
	String surname_en =  request.getParameter("surname_en");
	String gender =  request.getParameter("gender");
	String marital_status =  request.getParameter("marital_status"); //null
	
	String address =   request.getParameter("address"); //null
	String tel =  request.getParameter("tel"); //null'
	String email = request.getParameter("email");//null
	String facebook = request.getParameter("facebook");//null
	String picture = request.getParameter("picture");//null
	String father_name =  request.getParameter("father_name");//null
	String father_address =  request.getParameter("father_address");//null
	String father_tel = request.getParameter("father_tel");//null
	String mother_name =  request.getParameter("mother_name");//null
	String mother_address = request.getParameter("mother_address");//null
	String mother_tel = request.getParameter("mother_tel");//null
	String parent_name =  request.getParameter("parent_name");//null
	String parent_address =  request.getParameter("parent_address");//null
	String parent_tel =request.getParameter("parent_tel");//null
	String student_id = request.getParameter("student_id");
	String entrance_id = request.getParameter("entrance_id");
	String school = request.getParameter("school");//null
	String gpa = request.getParameter("gpa");//null
	
	String start_date_in = request.getParameter("start_date");
	// end_date_in = request.getParameter("end_date");
	String date_of_birth_in = request.getParameter("date_of_birth");
	
	Date start_date_dt = sdf.parse(start_date_in);
	Date end_date_dt  = new Date();
		 end_date_dt.setYear(start_date_dt.getYear()+8);
		 end_date_dt.setMonth(start_date_dt.getMonth());
		 end_date_dt.setDate(start_date_dt.getDate());
	Date birth_date_dt  = sdf.parse(date_of_birth_in);
	



	String start_date= sdf.format(start_date_dt);
	String end_date =  sdf.format(end_date_dt);
	String birth_date = sdf.format(birth_date_dt);
	
	
	System.out.println("date_now:"+date_now.toString());
	System.out.println("start_date:"+start_date);
	System.out.println("end_date:"+end_date_dt);
	System.out.println("birth_date:"+birth_date);
	
	
	
	String status_id = request.getParameter("status_id");
	String lecturer_name_th = request.getParameter("lecturer_name");			
	String lecturer_surname_th = request.getParameter("lecturer_surname");		
	String curriculum_id = request.getParameter("curriculum_id");
	String department_id_sel = request.getParameter("department_id");
	String full_name_id = request.getParameter("full_name_id");			
	String index = request.getParameter("index");						
	
	String faculty_id = "1";
	String lecturer_id = "";
	
	String title_en = null;
	

	String result = "";
 
	
	
    switch(title_th){	
		case "นาย" : title_en ="Mr."; break;
		case "นางสาว" : title_en ="Miss"; break;
		case "นาง" : title_en="Mrs."; break;
	}
	
		    
		    System.out.println("Phase1 Parameter");
		    System.out.println("name_th:"+name_th);
			System.out.println("surname_th:"+surname_th);
			String key_index_temp="";
	

			System.out.println("Data Before Add Student");
			System.out.println("Entrance_id:"+entrance_id);
			System.out.println("Curriculum id:"+curriculum_id);
			System.out.println("Department_id:"+department_id);
			System.out.println("StudentId:"+student_id);
			System.out.println("lecturer_id:"+lecturer_id);
			System.out.println("Status id :"+status_id);
			System.out.println("start_date:"+start_date);	
			System.out.println("end_date:"+end_date);
			
			
			
			try {
				// update full_name
				String sql_ful = "UPDATE `full_name` SET  "
						+ " `title_th`='"+title_th+"', `title_en`='"+title_en+"', "
						+ " `name_th`='"+name_th+"', `name_en`='"+name_en+"',  "
						+ " `surname_th`='"+surname_th+"', `surname_en`='"+surname_en+"' "
						+ " WHERE full_name_id='"+full_name_id+"'";
				connect.createStatement().executeUpdate(sql_ful);
				
				/* Log file */
				String log_ful = strUser+" "+name+" "+surname+" edit student";
				Log.writeUserFile(log_ful);
				Log.writeUserFile(sql_ful);	
				
				try{
					// update people
					String sql_peo = "UPDATE `people` SET `personal_id`='"+personal_id+"', `gender`='"+gender+"', " 
							+ " `marital_status`='"+marital_status+"', `date_of_birth`='"+date_of_birth_in+"', "
							+ " `phone_number`='"+tel+"', `address`='"+address+"', "
							+ " `email`='"+email+"', `facebook`='"+facebook+"'" 
							+ " WHERE `index`='"+index+"'";
					connect.createStatement().executeUpdate(sql_peo);
					
					/* Log file */
					Log.writeUserFile(sql_peo);	
					
					try{
						// update student
						
						// find index of lecturer 
						ResultSet rs_lec = connect.createStatement().executeQuery(" SELECT L.index "
								+ " FROM lecturer L , full_name N "
								+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'");
						
						if(rs_lec.next()) {
							lecturer_id = rs_lec.getString("index");
							
							
							
							String sql_stu = "UPDATE `student` SET `student_id`='"+student_id+"', `picture`='"+picture+"', "
									+ " `start_date`='"+start_date+"', `entrance_id`='"+entrance_id+"', `faculty_id`='"+faculty_id+"', "
									+ "  `gpa`='"+gpa+"', `lecturer_id`='"+lecturer_id+"', "
									+ "  `status_id`='"+status_id+"', `school`='"+school+"', "
									+ " `gpax`='"+gpa+"', `father_name`='"+father_name+"', `father_address`='"+father_address+"', "
									+ " `father_phone`='"+father_tel+"', `mother_name`='"+mother_name+"', `mother_address`='"+mother_address+"', "
									+ " `mother_phone`='"+mother_tel+"', `parent_name`='"+parent_name+"', `parent_address`='"+parent_address+"', "
									+ " `parent_phone`='"+parent_tel+"', `end_date`='"+end_date+"'"
									+ " WHERE `index`='"+index+"'";
							System.out.println(sql_stu);
							connect.createStatement().executeUpdate(sql_stu);		
							
							
							/* Log file */
							Log.writeUserFile(sql_stu);	
							
							// `department_id`='"+department_id+"', `curriculum_id`='"+curriculum_id+"',
							result = result + "success";
							HttpSession session_temp = request.getSession(true);
							session_temp.setAttribute("std_add_normal_photo","null");
			                	
							
							out.println(result);
						}
						else {
							result = result + "student";
							out.println(result);
						}
						
						
					} catch (Exception e) { 
						result = result + "student";
						out.println(result);
						
						System.out.println(result);  
						System.out.println(e);
					}
					
					
				} catch (Exception e) { 
					result = result + "people";
					out.println(result);
					
					System.out.println(result);  
					System.out.println(e);
				}

				
			} catch (Exception e) { 
				result = result + "full_name";
				out.println(result);
				
				System.out.println(result); 	
				System.out.println(e);
			}
			
			connect.close();
	
%>	
 